<?php
declare (strict_types = 1);

namespace app\command;

use think\console\Command;
use think\console\Input;
use think\console\input\Argument;
use think\console\input\Option;
use think\console\Output;
use \think\facade\Db;

// use PhpOffice\PhpSpreadsheet\IOFactory;

class Tool extends Command
{
    protected function configure()
    {
        // 指令配置
        $this->setName('Import')
            ->setDescription('the Import command');
    }

    protected function execute(Input $input, Output $output)
    {
        $file = "app/command/thisyear.xlsx";
        $reader = \PhpOffice\PhpSpreadsheet\IOFactory::createReader('Xlsx');
        $reader->setReadDataOnly(TRUE);
        $sheet = $reader->load($file);
        $worksheet = $sheet->getActiveSheet();
        $rows = $worksheet->getHighestRow();
        $columns = $worksheet->getHighestColumn();
        $highestColumnIndex = \PhpOffice\PhpSpreadsheet\Cell\Coordinate::columnIndexFromString($columns);
        
        for($row = 1; $row <= $rows; $row++) {
            $sql = "insert into admin_budget (district_code,district_name,institutions_code,institutions_name,departments_code,departments_name,project_category_code,project_category_name,project_code,project_name,functional_category_code,functional_category_name,functional_category_code_class,functional_category_name_class,functional_category_code_fund,functional_category_name_fund,functional_category_code_item,functional_category_name_item,economics_category_code,economics_category_name,economics_category_code_class,economics_category_name_class,economics_category_code_fund,economics_category_name_fund,total,fiscal_appropriation,general_public_budget,government_budgets,government_capital_management_budget,special_financial_account,utility_income,institution_management_income,institution_other_income,type) values (";
            for ($column = 1; $column <= $highestColumnIndex; $column++) {
                $val = $worksheet->getCellByColumnAndRow($column, $row)->getValue();
                $sql .= "'".$val."',";
            }
            $sql .= "'1')";
            echo $sql;
            echo "\n";
            Db::query($sql);
        }
        // 指令输出
        $output->writeln('Import Over.');
    }
}
